package com.it;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;


public class ContextServletTest extends HttpServlet {

    /**
     * Constructor of the object.
     */
    public ContextServletTest() {
        super();
    }

    /**
     * Destruction of the servlet. <br>
     */
    public void destroy() {
        super.destroy(); // Just puts "destroy" string in log
        // Put your code here
    }

    /**
     * The doGet method of the servlet. <br>
     *
     * This method is called when a form has its tag value method equals to get.
     *
     * @param request the request send by the client to the server
     * @param response the response send by the server to the client
     * @throws ServletException if an error occurred
     * @throws IOException if an error occurred
     */
    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        doPost(request,response);
    }

    /**
     * The doPost method of the servlet. <br>
     *
     * This method is called when a form has its tag value method equals to post.
     *
     * @param request the request send by the client to the server
     * @param response the response send by the server to the client
     * @throws ServletException if an error occurred
     * @throws IOException if an error occurred
     */
    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        ServletContext context = this.getServletContext();
        InputStream stream = context.getResourceAsStream("/WEB-INF/jdbc.properties");
        Properties  properties = new Properties();
        properties.load(stream);
        String dbName = properties.getProperty("dbName");
        String userName = properties.getProperty("userName");
        String userPassword = properties.getProperty("userPassword");
        String url = properties.getProperty("url");
        url = url+dbName+"?user="+userName+"&password="+userPassword;
        out.print(url);
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn =DriverManager.getConnection(url);
            Statement statement = conn.createStatement();
            String sql = "select * from student order by stuclass desc,number ASC";
            ResultSet rs = statement.executeQuery(sql);
            out.println("<HTML>");
            out.println("<table border=1>");
            out.println("<th colspan=4 style='font-size:x-large;alignment:center'>学生信息表</th>");
            out.println("<tr style='text-align:center'>");
            out.println("<td>学号</td><td>姓名</td><td>出生日期</td><td>所在班级</td></tr>");
            while(rs.next()){
                out.println("<tr style='text-align:center'>");
                out.println("<td>"+rs.getString(1)+"</td>");
                out.println("<td>"+rs.getString(2)+"</td>");
                out.println("<td>"+rs.getString("birthday")+"</td>");
                out.println("<td>"+rs.getString("stuclass")+"</td>");
            }
            out.println("  </BODY>");
            out.println("</HTML>");

        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace(out);
        }
        out.flush();
        out.close();
    }

    /**
     * Initialization of the servlet. <br>
     *
     * @throws ServletException if an error occurs
     */
    public void init() throws ServletException {
        // Put your code here
    }

}
